package com.swe.chums.dao.impl;

import com.swe.chums.dao.LessonsDao;
import com.swe.chums.dao.model.Lessons;
import com.swe.chums.dao.model.LessonsPk;
import com.swe.chums.dao.exceptions.LessonsDaoException;
import java.util.List;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.transaction.annotation.Transactional;

public class LessonsDaoImpl extends AbstractDAO implements ParameterizedRowMapper<Lessons>, LessonsDao
{
	protected SimpleJdbcTemplate jdbcTemplate;

	protected DataSource dataSource;

	/**
	 * Method 'setDataSource'
	 * 
	 * @param dataSource
	 */
	public void setDataSource(DataSource dataSource)
	{
		this.dataSource = dataSource;
		jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return LessonsPk
	 */
	@Transactional
	public LessonsPk insert(Lessons dto)
	{
		jdbcTemplate.update("INSERT INTO " + getTableName() + " ( webresource_id, title, url ) VALUES ( ?, ?, ? )",dto.getWebresourceId(),dto.getTitle(),dto.getUrl());
		LessonsPk pk = new LessonsPk();
		pk.setId( jdbcTemplate.queryForInt("select last_insert_id()") );
		return pk;
	}

	/** 
	 * Updates a single row in the lessons table.
	 */
	@Transactional
	public void update(LessonsPk pk, Lessons dto) throws LessonsDaoException
	{
		jdbcTemplate.update("UPDATE " + getTableName() + " SET id = ?, webresource_id = ?, title = ?, url = ? WHERE id = ?",dto.getId(),dto.getWebresourceId(),dto.getTitle(),dto.getUrl(),pk.getId());
	}

	/** 
	 * Deletes a single row in the lessons table.
	 */
	@Transactional
	public void delete(LessonsPk pk) throws LessonsDaoException
	{
		jdbcTemplate.update("DELETE FROM " + getTableName() + " WHERE id = ?",pk.getId());
	}

	/**
	 * Method 'mapRow'
	 * 
	 * @param rs
	 * @param row
	 * @throws SQLException
	 * @return Lessons
	 */
	public Lessons mapRow(ResultSet rs, int row) throws SQLException
	{
		Lessons dto = new Lessons();
		dto.setId( rs.getInt( 1 ) );
		dto.setWebresourceId( rs.getInt( 2 ) );
		dto.setTitle( rs.getString( 3 ) );
		dto.setUrl( rs.getString( 4 ) );
		return dto;
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "spring2013g3.lessons";
	}

	/** 
	 * Returns all rows from the lessons table that match the criteria 'id = :id'.
	 */
	@Transactional
	public Lessons findByPrimaryKey(int id) throws LessonsDaoException
	{
		try {
			List<Lessons> list = jdbcTemplate.query("SELECT id, webresource_id, title, url FROM " + getTableName() + " WHERE id = ?", this,id);
			return list.size() == 0 ? null : list.get(0);
		}
		catch (Exception e) {
			throw new LessonsDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the lessons table that match the criteria ''.
	 */
	@Transactional
	public List<Lessons> findAll() throws LessonsDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, webresource_id, title, url FROM " + getTableName() + " ORDER BY id", this);
		}
		catch (Exception e) {
			throw new LessonsDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the lessons table that match the criteria 'webresource_id = :webresourceId'.
	 */
	@Transactional
	public List<Lessons> findByWebResource(int webresourceId) throws LessonsDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, webresource_id, title, url FROM " + getTableName() + " WHERE webresource_id = ?", this,webresourceId);
		}
		catch (Exception e) {
			throw new LessonsDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the lessons table that match the criteria 'id = :id'.
	 */
	@Transactional
	public List<Lessons> findWhereIdEquals(int id) throws LessonsDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, webresource_id, title, url FROM " + getTableName() + " WHERE id = ? ORDER BY id", this,id);
		}
		catch (Exception e) {
			throw new LessonsDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the lessons table that match the criteria 'webresource_id = :webresourceId'.
	 */
	@Transactional
	public List<Lessons> findWhereWebresourceIdEquals(int webresourceId) throws LessonsDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, webresource_id, title, url FROM " + getTableName() + " WHERE webresource_id = ? ORDER BY webresource_id", this,webresourceId);
		}
		catch (Exception e) {
			throw new LessonsDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the lessons table that match the criteria 'title = :title'.
	 */
	@Transactional
	public List<Lessons> findWhereTitleEquals(String title) throws LessonsDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, webresource_id, title, url FROM " + getTableName() + " WHERE title = ? ORDER BY title", this,title);
		}
		catch (Exception e) {
			throw new LessonsDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the lessons table that match the criteria 'url = :url'.
	 */
	@Transactional
	public List<Lessons> findWhereUrlEquals(String url) throws LessonsDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, webresource_id, title, url FROM " + getTableName() + " WHERE url = ? ORDER BY url", this,url);
		}
		catch (Exception e) {
			throw new LessonsDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns the rows from the lessons table that matches the specified primary-key value.
	 */
	public Lessons findByPrimaryKey(LessonsPk pk) throws LessonsDaoException
	{
		return findByPrimaryKey( pk.getId() );
	}

        @Override
        public List<Lessons> selectLessons(String searchText) throws LessonsDaoException {
                
                String[] words = searchText.split(" ");
                String selectQuery = "SELECT id, webresource_id, title, url FROM " + getTableName() + " WHERE title  COLLATE UTF8_GENERAL_CI LIKE '%" + words[0] + "%' ";
                
                for (int i=1; i<words.length; i++) {
                    selectQuery += " and title COLLATE UTF8_GENERAL_CI LIKE '%" + words[i] + "%' ";
                }
                
                for (int i=0; i<words.length; i++) {
                    selectQuery += " UNION " + "SELECT id, webresource_id, title, url FROM " + getTableName() + " WHERE title  COLLATE UTF8_GENERAL_CI LIKE '%" + words[i] + "%' ";
                }
                try {
			return jdbcTemplate.query(selectQuery, this);
		}
		catch (Exception e) {
			throw new LessonsDaoException("Query failed", e);
		}
        }

}
